/** Display Database ResultSet in JTable.

     The JTable Constructors:
	JTable(): Constructs a default JTable which is initialized with a default data model,
		  a default column model, and a default selection model. 
	JTable(int numRows, int numColumns): Constructs a JTable with numRows and numColumns of empty
		  cells using the DefaultTableModel. 
	JTable(Object[][] rowData, Object[] columnNames): Constructs a JTable to display the values in
		  the two dimensional array, rowData, with column names, columnNames. 
	JTable(TableModel dm): Constructs a JTable which is initialized with dm as the data model,
		a default column model, and a default selection model. 
	JTable(TableModel dm, TableColumnModel cm): Constructs a JTable which is initialized with dm
		as the data model, cm as the column model, and a default selection model. 
	JTable(TableModel dm, TableColumnModel cm, ListSelectionModel sm): Constructs a JTable which is
		initialized with dm as the data model, cm as the column model, and sm as the selection model. 
	JTable(Vector rowData, Vector columnNames): Constructs a JTable to display the values in the
		Vector of Vectors, rowData, with column names, columnNames.
*/

import java.sql.*;
import java.io.*;
import javax.swing.*;
import java.awt.event.*;
import java.util.*;
import java.awt.*;

public class Addressbook extends CloseableJFrame implements ActionListener {
	// Database related objects
	Connection cnn;
	Statement  stmt;
	ResultSet  res;
	ResultSetMetaData meta;
	Vector<String>  colNames ;
	Vector<Vector<String>>	rows;	// Vector of Vectors (All columns of a row will be a vector)
	JTable	grid;
	JScrollPane srpnGrid = null;

	// GUI related objects
	String sel	= "SELECT LastName || ', '  || FirstName  Name "  +
			 ", HomePhone, WorkPhone, CellPhone, Street, Email ";
	String from	= " FROM Address ";
	String order	= " Order by Name ";
	String sql 	= null;
	Container c;
	JLabel  lblSQL = new JLabel("SELET", SwingConstants.RIGHT);
	JLabel  lblFilter = new JLabel(" Filter", SwingConstants.RIGHT);
	JTextField tfSQL = new JTextField(sel, 50);
	JTextField tfFilter = new JTextField( "Wang", 8);
	// String strArr[] = new String[10]; 
	// JComboBox tfSQL = new JComboBox( );
	JPanel  pnSouth = new JPanel();

	String url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
		 user="address", passwd="YinWuZhou";

	public Addressbook() {
		try {
 			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			cnn = DriverManager.getConnection(url, user, passwd);
			stmt = cnn.createStatement();
		   } catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }
		c = getContentPane(); c.setLayout(new BorderLayout());
		tfSQL.setEditable(true);
		tfSQL.addActionListener(this);
		tfFilter.addActionListener(this);
		// tfSQL.addItem("select * from test");
		pnSouth.add(lblSQL); pnSouth.add(tfSQL);
		pnSouth.add(lblFilter); pnSouth.add(tfFilter);
		c.add(pnSouth, BorderLayout.SOUTH);
		setSize(750, 400);
		setTitle("Enter a SQL Statement");
		setVisible(true);
		
		boolean selectedStatement = executeSQL();
		if (! selectedStatement ) return;
	}

	public static void main(String argv[]) {
		Addressbook ex4 = new Addressbook(); // get connected, and create stmt.
	}

//	public void paint(Graphics g) {
//		super.paint(g);
//	}

	public void actionPerformed (ActionEvent e) {
		/*
		int colCnt = 0;
		String name = tfFilter.getText().trim() + "%";
		sql = ((String) tfSQL.getText() ).trim() + from + " WHERE " +
			" FirstName like '" + name + "' OR " +
			 "LastName like '" + name + "' OR " + 
			 "Organization like '" + name + "' " ; 
	
		// execute a select or update statement?
		*/
		boolean selectedStatement = executeSQL();
		if (! selectedStatement ) return;
		// try { colCnt = meta.getColumnCount(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); }

		/*
		rows = new Vector<Vector<String>> (); colNames = new Vector<String>();
		fillColumnNameVector(colNames, colCnt);
		fillRows(rows, colCnt);

		setTitle("Result of " + ((String) tfSQL.getText()).trim());
		// setTitle("Result of " + ((String) tfSQL.getSelectedItem()).trim());
		if (srpnGrid != null ) c.remove(srpnGrid);
		grid = new JTable(rows, colNames);
		srpnGrid = new JScrollPane(grid);
		c.add(srpnGrid, BorderLayout.CENTER);
		validate();
		//repaint();
		*/
	}

	boolean executeSQL() {

	    int colCnt = 0;

	    String name = tfFilter.getText().trim() + "%";
	
	    sql = ((String) tfSQL.getText() ).trim() + from + " WHERE " + " FirstName like '" + name + "' OR " +
			 "LastName like '" + name + "' OR " + "Organization like '" + name + "' " ; 
	
	   try {
		if ( sql.charAt(sql.length() -1) == ';' ) sql = sql.substring(0, sql.length() -1);
		boolean selectStatement = sql.substring(0, 6).toUpperCase().equals("SELECT");
		int     count = 0;

		if ( !selectStatement ) {
	       	       count = stmt.executeUpdate(sql);
		       System.out.printf("%d record(s) affected by '%s'\n", count, sql);
		       sql = "SELECT * FROM TEST";
		       tfSQL.setText( sql );
		       // return false;
		}	       
		res = stmt.executeQuery(sql);
		meta = res.getMetaData();
		// if ( tfSQL.getSelectedIndex() == -1 ) tfSQL.addItem( sql );

		try { colCnt = meta.getColumnCount(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); }
		rows = new Vector<Vector<String>> (); colNames = new Vector<String>();
		fillColumnNameVector(colNames, colCnt);
		fillRows(rows, colCnt);

		setTitle("Result of " + ((String) tfSQL.getText()).trim());
		// setTitle("Result of " + ((String) tfSQL.getSelectedItem()).trim());
		if (srpnGrid != null ) c.remove(srpnGrid);
		grid = new JTable(rows, colNames);
		srpnGrid = new JScrollPane(grid);
		c.add(srpnGrid, BorderLayout.CENTER);
		validate();
	   } catch (SQLException e) { System.err.println("\nError occured in executing SQL: \n\t" + sql + "\n");
	   				return false;
	   }
	   return true;
	}

	void fillRows(Vector<Vector<String>> v, int colCnt) {
	   try {
		while ( res.next() ) {
			Vector<String> t = new Vector<String>();
			for ( int i = 1; i <= colCnt; i ++ ) t.add( res.getString(i)) ;
			v.add( t);
		}	
	   } catch (SQLException e) { e.printStackTrace();  /*System.exit(-1);*/ }
	     catch (Exception e2) { e2.printStackTrace();  /*System.exit(-1);*/ }
	}

	void fillColumnNameVector(Vector<String> v, int cnt) {
	   try {
		for ( int i = 1; i <= cnt; i ++ ) v.add(meta.getColumnName(i));
	   } catch (SQLException e ) { e.printStackTrace(); }
	
	}
	
	protected void finalize() {
	   try {
		stmt.close();
		res.close();
		cnn.close();
	   } catch (SQLException e) {}
	   try { super.finalize(); } catch (Throwable e) { e.printStackTrace(); }
	}
}
